#include "sql.h"

sqlite3 *db    = NULL;
char *Erro_Msg = NULL;

void sql_init()
{
	int ret;

	ret = sqlite3_open("/home/work/QT/smart_home/app/smart.db", &db);
	if(ret)
	{
		sys_error(sqlite3_errmsg(db));
	}
	else
	{
		//printf("samrt.db open success!\n");
	}
}

int sql_update_zb_msg(const char *table, unsigned int cmd, unsigned cmd_type)
{
    int ret=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	sprintf(sql, "update %s set status=%d where ID=%d;", table, cmd, cmd_type);

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		ret=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);
}

int sql_select_msg(const char *table, int r, int c)//获取某一行莫一列的值
{
    int ret,res=0;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;
    const unsigned char *pTmp;

    poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

    sprintf(sql, "select * from %s;", table);

    ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret != SQLITE_OK)
    {
        printf("QTTest:sqlite3_prepare_v2 error\n");
    }

    while(sqlite3_step(pstmt)==SQLITE_ROW)
    {
        if(sqlite3_column_int(pstmt, 0)==r)
        {
             res = sqlite3_column_int(pstmt, c);
             break;
        }
    }

    sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

//col:多列数组的列数 n表示col大小 buf:读取到的数据
int sql_get_table_msg_int(const char *table, int *col, int n, int buf[][2])
{
    int ret,i=0,j;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

    poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

    sprintf(sql, "select * from %s;", table);

    ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {

        while(sqlite3_step(pstmt)==SQLITE_ROW)
        {
			/*for(j=0; j<n; ++j)
			{
			    printf("sql:97 i=%d j=%d\n", i, j);
				buf[0][0] = sqlite3_column_int(pstmt, 1);//type
				printf("sql:99\n");
			}*/
            buf[i][0] = sqlite3_column_int(pstmt, col[0]);//type
            buf[i][1] = sqlite3_column_int(pstmt, col[1]);//type
            i++;
        }
    }
    else
    {
        i=-1;
    }

    sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

    return i;
}

//col:多列数组的列数 n表示col大小 buf:读取到的数据
int sql_get_table_msg_str(const char *table, int *col, int n, char buf[][3][1024])
{
    int ret,i=0,j;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

    poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

    sprintf(sql, "select * from %s;", table);

    ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
        while(sqlite3_step(pstmt)==SQLITE_ROW)
        {
			for(j=0; j<n; ++j)
			{
				if(strcpy(buf[i][j], (char *)sqlite3_column_text(pstmt, col[j]))==NULL)
				{
					printf("sql_get_table_msg_str:%s\n", buf[i][j]);
					i=-1;
					break;
				}
			}

			if(i==-1)
			{
				break;
			}
			else
			{
				i++;
			}
        }
    }

    sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

    return i;
}

/*********************************************************************
 * @fn      sql_get_table_nfield
 *
 * @brief   获取表中n个字段的值 以字符串的形式
 *
 * @param   table:表名
 * @param   col:列数组
 * @param   type:每一个字段的类型（SQL_FIEL_TYPE_INT/SQL_FIEL_TYPE_STR）
 * @param   n:type和col数组的长度
 * @param   buf:存取结果的数组
 *
 * @return  成功返回获取到的行数 失败返回-1
 */
int sql_get_table_nfield(const char *table, int *col, char *type, int n, char buf[][10][256])
{
	int ret,i,j,result;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

	i=0;

	//等待信号
    poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	//执行语句
    sprintf(sql, "select * from %s;", table);

    ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
        while(sqlite3_step(pstmt)==SQLITE_ROW)
        {
            //printf("sql_get_table_nfield i=%d\n", i);
			for(j=0; j<n; ++j)
			{
			    //printf("sql_get_table_nfield j=%d\n", j);
				if(type[j]==SQL_FIEL_TYPE_STR)
				{
					if(strcpy(buf[i][j], (char *)sqlite3_column_text(pstmt, col[j]))==NULL)
					{
						//printf("sql_get_table_msg_str:%s\n", buf[i][j]);
						i=-1;
						break;
					}
				}
				else if(type[j]==SQL_FIEL_TYPE_INT)
				{
					result = sqlite3_column_int(pstmt, col[j]);
					sprintf(buf[i][j], "%d", result);
				}
			}

			if(i==-1)
			{
				break;
			}
			else
			{
				i++;
			}
        }
    }

    sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

    return i;
}

int sql_logic_table_judge(const char *table, const char *name, const char *password)
{
	int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[1024];
    const char *pzTail;

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	sprintf(sql, "select PASSWORD from %s where ID=\"%s\";", table, name);

	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		printf("255 yes!\n");
		while(sqlite3_step(pstmt)==SQLITE_ROW)
		{
			res = strcmp(password, (char *)sqlite3_column_text(pstmt, 0));
		}
    }

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);


	return res;
}

int sql_query_value_by_id(const char *table, const char *field, const int id)
{
    int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;
    const unsigned char *pTmp;

    //printf("153\n");

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	sprintf(sql, "select %s from %s where ID=%d;", field, table, id);
	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		if(sqlite3_step(pstmt)==SQLITE_ROW)
		{
			res = sqlite3_column_int(pstmt, 0);
			//printf("%s id=%d %s=%d\n", table, id, field, res);
		}
	}

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

int insert_line_data(const char *table, const char data_buf[][32], char type[], int len)
{
	int res=0,i,count=0;
	char sql[256];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	//INSERT INTO DeviceCfg VALUES("AD:BA:CF:EF:12:34:56:89", "none", "none");
	count = sprintf(sql, "INSERT INTO %s VALUES(", table);

	for(i=0; i<len-1; ++i)
	{
		if(type[i]==SQL_FIEL_TYPE_STR)
		{
			count += sprintf(sql+count, "\"%s\",", data_buf[i]);
		}
		else if(type[i]==SQL_FIEL_TYPE_INT)
		{
			count += sprintf(sql+count, "%s,", data_buf[i]);
		}
	}

	if(type[i]==SQL_FIEL_TYPE_STR)
	{
		count += sprintf(sql+count, "\"%s\");", data_buf[i]);
	}
	else if(type[i]==SQL_FIEL_TYPE_INT)
	{
		count += sprintf(sql+count, "%s);", data_buf[i]);
	}

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

int update_field_value_by_id(const char *table, const char *field, const int value, const int id)
{
    int res=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	sprintf(sql, "update %s set %s=%d where ID=%d;", table, field, value, id);

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

int update_field_value_by_str(const char *table, const char *field_name, const char *filed_value, const char *str_name, const char *str_value)
{
    int res=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	sprintf(sql, "update %s set %s=\"%s\" where %s=\"%s\";", table, str_name, str_value, field_name, filed_value);

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}



/*********************************************************************
 * @fn      update_intfield_value_by_strfield
 *
 * @brief   更新表格中的int字段，通过str字段进行定位
 *
 * @param   table:索引
 * @param   strfield_name:str字段名
 * @param   strfield_value:str字段值
 * @param   intfield_name:int字段名
 * @param   intfield_value:int字段值
 *
 * @return  成功返回0 失败返回-1
 */
int update_intfield_value_by_strfield(const char *table, const char *strfield_name, const char *strfield_value, const char *intfield_name, const int intfield_value)
{
	int res=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	sprintf(sql, "update %s set %s=%d where %s=\"%s\";", table, intfield_name, intfield_value, strfield_name, strfield_value);

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      update_intfield_value_by_intfield
 *
 * @brief   更新表格中的int字段，通过str字段进行定位
 *
 * @param   table:索引
 * @param   intfield_name:定位字段
 * @param   intfield_value:定位字段值
 * @param   uintfield_name:int更新字段名
 * @param   uintfield_value:int更新字段值
 *
 * @return  成功返回0 失败返回-1
 */
int update_intfield_value_by_intfield(const char *table, const char *intfield_name, const int intfield_value, const char *uintfield_name, const int uintfield_value)
{
	int res=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	sprintf(sql, "update %s set %s=%d where %s=%d;", table, uintfield_name, uintfield_value, intfield_name, intfield_value);

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      update_intfield_value_by_intfield
 *
 * @brief   更新表格中的int字段，通过str字段进行定位
 *
 * @param   table:索引
 * @param   update_field_name:更新字段名
 * @param   update_field_value:更新字段值
 * @param   update_field_type:更新字段类型
 * @param   location_field_name:更新字段名
 * @param   location_field_value:更新字段值
 * @param   location_field_type:更新字段类型
 *
 * @return  成功返回0 失败返回-1
 */
int update_field_value(const char *table, const char *update_field_name, const char *update_field_value, const char update_field_type, const char *location_field_name, const char *location_field_value, const char location_field_type)
{
	int res=0,count=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	count = sprintf(sql, "update %s ", table);

	if(update_field_type==SQL_FIEL_TYPE_INT)
    {
       count += sprintf(sql+count, "set %s=%s ", update_field_name, update_field_value);
    }
    else if(update_field_type==SQL_FIEL_TYPE_STR)
    {
        count += sprintf(sql+count, "set %s=\"%s\" ", update_field_name, update_field_value);
    }

    if(location_field_type==SQL_FIEL_TYPE_INT)
    {
       count += sprintf(sql+count, "where %s=%s;", location_field_name, location_field_value);
    }
    else if(location_field_type==SQL_FIEL_TYPE_STR)
    {
        count += sprintf(sql+count, "where %s=\"%s\";", location_field_name, location_field_value);
    }

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}


/*********************************************************************
 * @fn      sql_query_field_exist
 *
 * @brief   查询某个字段的值是否存在
 *
 * @param   table:索引
 * @param   field:查询的字段名
 * @param   field_value:查询的字段的值
 * @param   type:字段类型
 *
 * @return  存在返回0 不存在返回-1
 */
int sql_query_field_exist(const char *table, const char *field, const void *field_value, const char type)
{
    int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	if(type == SQL_FIEL_TYPE_STR)
	{
		sprintf(sql, "select * from %s where %s=\"%s\";", table, field, (const char *)field_value);
	}
	else if(type == SQL_FIEL_TYPE_INT)
	{
		sprintf(sql, "select * from %s where %s=%d;", table, field, *((const int *)field_value));
	}

	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		if(sqlite3_step(pstmt)==SQLITE_ROW)
		{
			res = 0;
			//printf("%s id=%d %s=%d\n", table, id, field, res);
		}
	}

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      sql_query_intfield_by_strfield
 *
 * @brief   使用str字段查询int字段的值
 *
 * @param   table:索引
 * @param   intfield_name:查询的字段名
 * @param   strfield_name:使用的str字段
 * @param   strfield_value:str字段的值
 *
 * @return  成功返回int字段的值 失败返回-1
 */
int sql_query_intfield_by_strfield(const char *table, const char *intfield_name, const char *strfield_name, const char *strfield_value)
{
    int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

    //printf("153\n");

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	sprintf(sql, "select %s from %s where %s=\"%s\";", intfield_name, table, strfield_name, strfield_value);
	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		if(sqlite3_step(pstmt)==SQLITE_ROW)
		{
			res = sqlite3_column_int(pstmt, 0);
			//printf("%s id=%d %s=%d\n", table, id, field, res);
		}
	}

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      sql_query_strfield_by_strfield
 *
 * @brief   使用str字段查询str字段的值
 *
 * @param   table:索引
 * @param   qfield_name:查询的字段名
 * @param   qstrfield_value:查询字段的值
 * @param   strfield_name:使用的str字段
 * @param   strfield_value:str字段的值
 *
 * @return  成功返回0 失败返回-1
 */
int sql_query_strfield_by_strfield(const char *table, const char *qstrfield_name, char *qstrfield_value, const char *strfield_name, const char *strfield_value)
{
    int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	sprintf(sql, "select %s from %s where %s=\"%s\";", qstrfield_name, table, strfield_name, strfield_value);
	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		if(sqlite3_step(pstmt)==SQLITE_ROW)
		{
			if(strcpy(qstrfield_value, (char *)sqlite3_column_text(pstmt, 0))!=NULL)
			{
				res=0;
			}
			//printf("%s id=%d %s=%d\n", table, id, field, res);
		}
	}

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      sql_delete_line
 *
 * @brief   删除表格中的一条数据
 *
 * @param   table:表名
 * @param   primary_fieldname:主键名
 * @param   primary_fieldvalue:主键值
 * @param   type:主键类型
 *
 * @return  成功返回0 失败返回-1
 */
int sql_delete_line(const char *table, const char *primary_fieldname, const char *primary_fieldvalue, const char type)
{
	int res=0;
    //sqlite3_stmt *pstmt;
    char sql[128];
    //const char *pzTail;

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	if(type==SQL_FIEL_TYPE_STR)
	{
		sprintf(sql, "delete from %s where %s = \"%s\";", table, primary_fieldname, primary_fieldvalue);
	}
	else if(type==SQL_FIEL_TYPE_INT)
	{
		sprintf(sql, "delete from %s where %s = %s;", table, primary_fieldname, primary_fieldvalue);
	}

	//printf("del_sql = %s\n", sql);

	//ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
    if(Erro_Msg!=NULL)
    {
		res = -1;
	}

	//sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      sql_update_column
 *
 * @brief   设置表格中一列的值
 *
 * @param   table:表名
 * @param   col_name:列名
 * @param   col_value:列值
 * @param   type:主键类型
 *
 * @return  成功返回0 失败返回-1
 */
int sql_update_column(const char *table, const char *col_name, const char *col_value, const char type)
{
	int res=0;
	char sql[128];

	poperation(sem_sqlite_wr_id, g_sem_id);
	sql_init();

	if(type == SQL_FIEL_TYPE_INT)
	{
		sprintf(sql, "update %s set %s=%s;", table, col_name, col_value);
	}
	else if(type == SQL_FIEL_TYPE_STR)
	{
		sprintf(sql, "update %s set %s=\"%s\";", table, col_name, col_value);
	}

	sqlite3_exec(db, sql, 0, 0, &Erro_Msg);
	if(Erro_Msg!=NULL)
	{
		res=-1;
	}

	sqlite3_close_v2(db);
	voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}

/*********************************************************************
 * @fn      sql_get_intfield_maxvalue
 *
 * @brief   获取表格中某个字段的最大值
 *
 * @param   table:表名
 * @param   fieldname:字段名
 *
 * @return  成功返回该字段的最大值 失败返回-1
 */
int sql_get_intfield_maxvalue(const char *table, const char *fieldname)
{
    int ret,res=-1;
    sqlite3_stmt *pstmt;
    char sql[128];
    const char *pzTail;

	poperation(sem_sqlite_wr_id, g_sem_id);
    sql_init();

	sprintf(sql, "select max(%s) from %s;", fieldname, table);
	ret = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &pzTail);
    if(ret == SQLITE_OK)
    {
		if(sqlite3_step(pstmt)==SQLITE_ROW)
		{
		    res = sqlite3_column_int(pstmt, 0);
		}
	}

	sqlite3_finalize(pstmt);
    sqlite3_close_v2(db);
    voperation(sem_sqlite_wr_id, g_sem_id);

	return res;
}


